﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtility;

namespace SQLServerDAL.Sys
{
    public class EnumDef
    {
        public EnumDef() { }
        public string Add(Model.Sys.EnumDef _obj)
        {
            string result = "";

            string sqlstring = "INSERT INTO EnumDef (EnumDefId,ApplicationId,EnumKey,Type,Title,Description,IsMaster)";
            sqlstring += "VALUES(NEWID(),@ApplicationId,@EnumKey,@Type,@Title,@Description,@IsMaster)";
            SqlParameter[] prams = {
			    SqlHelper.CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, new Guid(_obj.ApplicationId)),
				SqlHelper.CreateInputParam("@EnumKey", SqlDbType.VarChar, _obj.EnumKey),
				SqlHelper.CreateInputParam("@Type", SqlDbType.VarChar, _obj.Type),
				SqlHelper.CreateInputParam("@Title", SqlDbType.VarChar, _obj.Title),
				SqlHelper.CreateInputParam("@Description", SqlDbType.VarChar, _obj.Description),
				SqlHelper.CreateInputParam("@IsMaster", SqlDbType.Bit, _obj.IsMaster ? 1 : 0)
            };

            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlstring, prams);
                result = "succeeded";
            }
            catch (Exception ex)
            {
                result = ex.ToString();
            }

            return result;
        }

        public string Update(Model.Sys.EnumDef _obj)
        {
            string result = "";

            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("UPDATE EnumDef SET ");
                    strSql.Append("ApplicationId = @ApplicationId,");
                    strSql.Append("EnumKey = @EnumKey,");
                    strSql.Append("Type = @Type,");
                    strSql.Append("Title = @Title,");
                    strSql.Append("Description = @Description,");
                    strSql.Append("IsMaster = @IsMaster");
                    strSql.Append(" WHERE EnumDefId = @EnumDefId;");
                    SqlParameter[] prams = {
				        SqlHelper.CreateInputParam("@EnumDefId", SqlDbType.UniqueIdentifier, new Guid(_obj.EnumDefId)),
				        SqlHelper.CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, new Guid(_obj.ApplicationId)),
				        SqlHelper.CreateInputParam("@EnumKey", SqlDbType.VarChar, _obj.EnumKey),
				        SqlHelper.CreateInputParam("@Type", SqlDbType.VarChar, _obj.Type),
				        SqlHelper.CreateInputParam("@Title", SqlDbType.VarChar, _obj.Title),
				        SqlHelper.CreateInputParam("@Description", SqlDbType.VarChar, _obj.Description),
				        SqlHelper.CreateInputParam("@IsMaster", SqlDbType.Bit, _obj.IsMaster ? 1 : 0)
					};

                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql.ToString(), prams);

                    trans.Commit();
                    result = "succeeded";
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                    trans.Rollback();
                    throw ex;
                }
            }
            conn.Close();

            return result;
        }

        public Model.Sys.EnumDef GetObjById(string enumDefId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM EnumDef WHERE EnumDefId = @EnumDefId");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@EnumDefId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(enumDefId);

            Model.Sys.EnumDef _obj = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _obj = new Model.Sys.EnumDef();
                    _obj.EnumDefId = rd["EnumDefId"].ToString();
                    _obj.ApplicationId = rd["ApplicationId"].ToString();
                    _obj.EnumKey = rd["EnumKey"].ToString();
                    _obj.Type = rd["Type"].ToString();
                    _obj.Title = rd["Title"].ToString();
                    _obj.Description = rd["Description"].ToString();
                    if (rd["IsMaster"].ToString() != "")
                    {
                        if ((rd["IsMaster"].ToString() == "1") || (rd["IsMaster"].ToString().ToLower() == "true"))
                        {
                            _obj.IsMaster = true;
                        }
                        else
                        {
                            _obj.IsMaster = false;
                        }
                    }
                }
            }
            catch
            {
                return null;
            }
            return _obj;
        }

        public void Delete(string enumDefId)
        {
            StringBuilder strSql = new StringBuilder();
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@EnumDefId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(enumDefId);

            strSql.Append("DELETE FROM EnumDef WHERE [EnumDefId] = @EnumDefId;");

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
        }

        public bool ValExisted(string enumDefId, string enumKey)
        {
            StringBuilder strSql = new StringBuilder();
            SqlParameter[] parameters = new SqlParameter[] { 
                SqlHelper.CreateInputParam("@EnumDefId", SqlDbType.UniqueIdentifier, new Guid(enumDefId)),
				SqlHelper.CreateInputParam("@EnumKey", SqlDbType.VarChar, enumKey)
            };
            strSql.Append("SELECT COUNT(1) FROM EnumDef WHERE [EnumDefId] <> @EnumDefId AND EnumKey = @EnumKey;");

            object _obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
            if (_obj != null && (int)_obj > 0)
            {
                return true;
            }
            else
                return false;
        }
    }
}
